package com.zsrt.oa.employee.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.zsrt.common.entity.LeftMenuEntity;
import com.zsrt.oa.dept.entity.DeptEntity;
import com.zsrt.oa.employee.dao.EmployeeDao;
import com.zsrt.oa.employee.entity.EmployeeEntity;
import com.zsrt.utils.DBUtils;

/**
 * 对employee的操作实现类
 * @author 罗智壕-13424431416
 *
 * 2017年5月24日
 */
public class EmployeeDaoImpl implements EmployeeDao{
	
	/**
	 * 增加1个雇员
	 * @param entity 传入增加的对象
	 * @return 影响的记录的条数
	 */
	public int add(EmployeeEntity entity) throws SQLException{
		Connection con = DBUtils.getMessage();
		PreparedStatement ps = null;
		
		String empId = "select employee_s.nextval as empId from dual";
		ps = con.prepareStatement(empId);
		ResultSet resultSet = ps.executeQuery();
		int hobbiesEmpId = 0;
		while(resultSet.next()){
			hobbiesEmpId = resultSet.getInt("empId");
		}
		
		String sql = "insert into employee values(?,?,?,?,?,?)";
		ps = con.prepareStatement(sql);
		
		ps.setInt(1, hobbiesEmpId);
		ps.setString(2, entity.getDeptId());
		ps.setString(3, entity.getEmpPassword());
		ps.setString(4, entity.getEmpSex());
		ps.setString(5, entity.getEmpPhone());
		ps.setString(6, entity.getEmpRemarks());
		
		ps.executeUpdate();
		DBUtils.closeResources(con, ps);
		
		return hobbiesEmpId;
	}
	
	/**
	 * 分页查询
	 * @param min 从第几条开始显示
	 * @param max 第几条结束
	 * @return
	 */

	@Override
	public List<EmployeeEntity> query(int pageSize,int curPage,Map<String,Object> map) throws SQLException{
		
		List<EmployeeEntity> list = new ArrayList<EmployeeEntity>();
		
		Connection con = DBUtils.getMessage();
		PreparedStatement ps = null;
		
		String sql = "SELECT GET.ID AS empId,GET.DEPT_ID AS deptId,GET.PASSWORDAS as empPassword"
					+ " ,GET.SEX AS empSex,GET.PHONE AS empPhone,GET.REMARKS AS empRemarks"
					+ " FROM (SELECT l.ID as id,d.dept_name as DEPT_ID,l.password as PASSWORDAS,dd.children_name as SEX"
					+ " ,l.phone as PHONE,l.remarks as REMARKS,ROWNUM AS ROW_NUM FROM EMPLOYEE L "
					+ " inner join dept d on l.dept_id = d.id"
					+ " inner join OA_DD_DETAIL_T dd on l.sex = dd.id"
					+ " )GET WHERE GET.ROW_NUM>=? AND GET.ROW_NUM<=?";
		ps = con.prepareStatement(sql);
		
		ps.setInt(1, pageSize);
		ps.setInt(2, curPage);
		
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			EmployeeEntity entity = new EmployeeEntity();
			
			entity.setEmpId(rs.getString("empId"));
			entity.setDeptId(rs.getString("deptId"));
			entity.setEmpPassword(rs.getString("empPassword"));
			entity.setEmpSex(rs.getString("empSex"));
			entity.setEmpPhone(rs.getString("empPhone"));
			entity.setEmpRemarks(rs.getString("empRemarks"));
			
			list.add(entity);
		}
		
		String sql2 = "select count(*)/5 as cout from EMPLOYEE";
		ps = con.prepareStatement(sql2);
		ResultSet rsCount = ps.executeQuery();
		while(rsCount.next()){
			String count = rsCount.getString("cout");
			boolean result = DBUtils.isInteger(count);
			if(result){
				map.put("count", count);
			}else{
				Double dou = Double.valueOf(count);
				if(dou >= dou && dou <= dou+1){
					Integer integer = (int) (dou+1);
					count = String.valueOf(integer);
				}
				map.put("count", count);
			}
		}
		
		
		DBUtils.closeResources(con, ps ,rsCount);
		return list;
	}
	
	/**
	 * 用户增加下拉框显示部门
	 * @return
	 */
	@Override
	public List<DeptEntity> deptQuery()  throws SQLException{
		List<DeptEntity> list = new ArrayList<DeptEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "select id as deptId,dept_name as deptName from dept";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			DeptEntity entity = new DeptEntity();
			entity.setDeptId(rs.getString("deptID"));
			entity.setDeptName(rs.getString("deptName"));
			list.add(entity);
		}
		
		DBUtils.closeResources(con, ps, rs);
		return list;
	}
	
	/**
	 * 员工批量删除
	 * @param entity
	 * @throws SQLException
	 */
	@Override
	public void empDeletebByIds(EmployeeEntity entity) throws SQLException {
		Connection con = DBUtils.getMessage();
		PreparedStatement ps = null;
		String empId = entity.getEmpId();
		String[] split = empId.split(",");
		
		String sql = "delete from employee where id in (?)";
		ps = con.prepareStatement(sql);
		for(int index=0; index<split.length; index++){
			ps.setString(1, split[index]);
			ps.addBatch();
		}
		ps.executeBatch();
		
		DBUtils.closeResources(con, ps);
	}
	
	/**
	 * 根据id进行查询
	 * @param entity
	 * @throws SQLException
	 */
	@Override
	public void empFindById(EmployeeEntity entity) throws SQLException {
		Connection con = DBUtils.getMessage();
		String sql = "select id as empId,dept_id deptId,password as empPassword,"
				+ "sex as empSex,phone as empPhone,"
				+ "remarks as empRemarks from employee where id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getEmpId());
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			entity.setEmpId(rs.getString("empId"));
			entity.setDeptId(rs.getString("deptId"));
			entity.setEmpPassword(rs.getString("empPassword"));
			entity.setEmpSex(rs.getString("empSex"));
			entity.setEmpPhone(rs.getString("empPhone"));
			entity.setEmpRemarks(rs.getString("empRemarks"));
		}
		
		DBUtils.closeResources(con, ps, rs);
	}
	
	/**
	 * 员工修改
	 * @param entity
	 * @throws SQLException
	 */
	@Override
	public void empUpdate(EmployeeEntity entity) throws SQLException {
		Connection con = DBUtils.getMessage();
		String sql = "update employee set dept_id = ?,password = ?,"
				+ " sex = ?,phone = ?,remarks = ? where id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.setString(1, entity.getDeptId());
		ps.setString(2, entity.getEmpPassword());
		ps.setString(3, entity.getEmpSex());
		ps.setString(4, entity.getEmpPhone());
		ps.setString(5, entity.getEmpRemarks());
		ps.setString(6, entity.getEmpId());
		
		ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		
	}
	
	/**
	 * 登录校验
	 * @param entity
	 * @return
	 */
	@Override
	public int login(EmployeeEntity entity) throws SQLException{
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "select count(id) as QTY from employee where phone = ? and password = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getEmpPhone());
		ps.setString(2, entity.getEmpPassword());
		ResultSet rs = ps.executeQuery();
		
		if(rs.next()){
			counter = rs.getInt("QTY");
		}
		
		DBUtils.closeResources(con, ps, rs);
		return counter;
	}
	
	/**
	 * 动态生成菜单
	 * @param map
	 * @return
	 */
	@Override
	public List<LeftMenuEntity> leftMenuAppend(Map<String, Object> map) throws SQLException{
		List<LeftMenuEntity> list = new ArrayList<LeftMenuEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "select "
				+ "m.ID as menuId,"
				+ "m.menu_name as menuName,"
				+ "c.chrilden_name as chrildenName,"
				+ "m.url||c.url as menuUrl "
				+ "from oa_menu_t m "
				+ "inner join oa_menu_chrilden_t c "
				+ "on m.id = c.parent_id";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			LeftMenuEntity entity = new LeftMenuEntity();
			entity.setChrildentName(rs.getString("chrildenName"));
			entity.setMenuId(rs.getInt("menuId"));
			entity.setMenuName(rs.getString("menuName"));
			entity.setMenuUrl(rs.getString("menuUrl"));
			list.add(entity);
		}
		
		DBUtils.closeResources(con, ps, rs);
		return list;
	}
	
}
